import pymysql
import cx_Oracle

class MysqlDB:
    def __init__(self, host, port, user, password, database):
        """
        初始化数据库连接。
        
        :param host: 数据库主机地址
        :param port: 数据库端口
        :param user: 数据库用户名
        :param password: 数据库密码
        :param database: 数据库名称
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = None

    def connect(self):
        """建立数据库连接。"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4'
            )
            
            print("Successfully connected to the database.")
        except pymysql.MySQLError as e:
            print("Error while connecting to MySQL:", e)

    def close(self):
        """关闭数据库连接。"""
        if self.connection:
            self.connection.close()
            print("Database connection closed.")

    def insert(self, table, data):
        """
        插入数据到指定表中。
        
        :param table: 表名
        :param data: 字典形式的数据，键为列名，值为插入的数据
        """
        if not self.connection:
            print("Connection is not established. Call connect() first.")
            return

        # 构造 SQL 语句
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, tuple(data.values()))
            self.connection.commit()
            print(f"Data inserted successfully into {table}.")
        except pymysql.MySQLError as e:
            print("Error while inserting data:", e)
            self.connection.rollback()

    def query(self, query_sql):
        """
        从指定表中查询数据。
        
        :param table: 表名
        :param columns: 要查询的列名，默认为所有列
        :param where: 查询条件，默认为 None
        :return: 查询结果列表
        """
        if not self.connection:
            print("Connection is not established. Call connect() first.")
            return
        
        sql = query_sql

        try:
            with self.connection.cursor() as cursor:
                cursor.execute("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))")
                cursor.execute(sql)
                results = cursor.fetchall()  # 获取所有查询结果
                return results
        except pymysql.MySQLError as e:
            print("Error while selecting data:", e)
            return None



class OracleDB:
    def __init__(self, username, password, dsn):
        """
        初始化数据库连接
        :param username: 数据库用户名
        :param password: 数据库密码
        :param dsn: 数据库的DSN（数据源名称）
        """
        self.username = username
        self.password = password
        self.dsn = dsn
        self.connection = None
        self.cursor = None

    def connect(self):
        """
        创建数据库连接
        """
        try:
            self.connection = cx_Oracle.connect(self.username, self.password, self.dsn)
            self.cursor = self.connection.cursor()
            print("连接成功！")
        except cx_Oracle.DatabaseError as e:
            print(f"数据库连接失败: {e}")
            raise

    def query(self, sql_query):
        """
        执行查询操作
        :param sql_query: 要执行的 SQL 查询语句
        :return: 查询结果
        """
        if not self.connection:
            print("没有数据库连接！")
            return None

        try:
            self.cursor.execute(sql_query)
            result = self.cursor.fetchall()  # 获取所有结果
            return result
        except cx_Oracle.DatabaseError as e:
            print(f"查询失败: {e}")
            return None

    def close(self):
        """
        关闭数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        print("连接已关闭。")

# 示例用法
if __name__ == "__main__":
    db = MysqlDB(Config.mysql_host, Config.mysql_port, Config.mysql_user, Config.mysql_password, Config.mysql_database)
    db.connect()
    db.close()
